package com.excel.util;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CellUtils {

	private static FormulaEvaluator getEvaluator(Cell c) {
		Workbook book = c.getSheet().getWorkbook();
		if (book instanceof HSSFWorkbook) {
			return new HSSFFormulaEvaluator((HSSFWorkbook) book);
		} else if (book instanceof XSSFWorkbook) {
			return new XSSFFormulaEvaluator((XSSFWorkbook) book);
		}
		return null;
	}

	private final static DataFormatter formater = new DataFormatter();

	public static String getStringValue(Cell c) {
		if (c == null) {
			return "";
		}

		int type = c.getCellType();
		String value = "";

		switch (type) {
		case Cell.CELL_TYPE_FORMULA:
			FormulaEvaluator formulaEvaluator = getEvaluator(c);
			value = formater.formatCellValue(c, formulaEvaluator);
			break;
		case Cell.CELL_TYPE_ERROR:
			break;
		default:
			value = formater.formatCellValue(c);
		}

		return value;
	}

	@SuppressWarnings("unused")
	@Deprecated
	private static String getCellValueByType(Cell c, int type) {
		String value = "";
		switch (type) {
		case Cell.CELL_TYPE_STRING:
			value = c.getStringCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(c)) {
				value = c.getDateCellValue().toString();
				c.getCellStyle().getDataFormat();
			} else {
				value = c.getNumericCellValue() + "";
			}
			break;
		case Cell.CELL_TYPE_FORMULA:
			FormulaEvaluator formulaEvaluator = getEvaluator(c);
			int t = formulaEvaluator.evaluateFormulaCell(c);
			value = getCellValueByType(c, t);
			break;
		case Cell.CELL_TYPE_ERROR:
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = c.getBooleanCellValue() + "";
			break;
		}
		return value;
	}

	/**
	 * 合并单元格处理--加入list
	 * 
	 * @param sheet
	 * @return
	 */
	public static  void getCombineCell(Sheet sheet, List<CellRangeAddress> list) {
		// 获得一个 sheet 中合并单元格的数量
		int sheetmergerCount = sheet.getNumMergedRegions();
		// 遍历合并单元格
		for (int i = 0; i < sheetmergerCount; i++) {
			// 获得合并单元格加入list中
			CellRangeAddress ca = sheet.getMergedRegion(i);
			list.add(ca);
		}
	}

	public static CellRangeAddress getCombineRangeAddress(List<CellRangeAddress> listCombineCell,
			Cell cell, Sheet sheet) {
		int firstC = 0;
		int lastC = 0;
		int firstR = 0;
		int lastR = 0;
		for (CellRangeAddress ca : listCombineCell) {
			firstC = ca.getFirstColumn();
			lastC = ca.getLastColumn();
			firstR = ca.getFirstRow();
			lastR = ca.getLastRow();
			if (cell.getColumnIndex() <= lastC
					&& cell.getColumnIndex() >= firstC) {
				if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
					return ca;
				}
			}
		}
		return null;
	}
}
